package com.tlgen.utils;

import com.tlgen.model.Department;
import com.tlgen.model.Enlist;
import com.tlgen.model.Enroll;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: tlgen
 * @Discription:
 */
public class POIUtils {

    //enlsit enlist2Excel
    public static ResponseEntity<byte[]> enlist2Excel(List<Enlist> list) {
        //1.创建excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建文档摘要
        workbook.createInformationProperties();
        //3.获取并配置文档信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        //文档类别
        docInfo.setCategory("员工信息");
        //文档管理员
        docInfo.setManager("tlgen");
        //设置公司信息
        docInfo.setCompany("com.tlgen.com");
        //4.获取文档摘要信息
        SummaryInformation summInfo = workbook.getSummaryInformation();
        //文档标题
        summInfo.setTitle("员工信息表");
        //作者
        summInfo.setAuthor("tlgen");
        //备注
        summInfo.setComments("本文档由 tlgen 提供");

        //5.创建样式
        //标题行样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillBackgroundColor(IndexedColors.YELLOW.index); //背景色
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充模式
        HSSFCellStyle dateCellStyle = workbook.createCellStyle(); //日期格式
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
        HSSFSheet sheet = workbook.createSheet("招聘计划信息表");
        //设置列的宽度
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 12 * 256);
        sheet.setColumnWidth(4, 12 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 10 * 256);
        sheet.setColumnWidth(7, 10 * 256);
        sheet.setColumnWidth(8, 16 * 256);

        //6. 创建标题行
        HSSFRow r0 = sheet.createRow(0);
        HSSFCell c0 = r0.createCell(0);
        c0.setCellValue("编号");
        c0.setCellStyle(headerStyle);
        HSSFCell c1 = r0.createCell(1);
        c1.setCellStyle(headerStyle);
        c1.setCellValue("计划名称");
        HSSFCell c2 = r0.createCell(2);
        c2.setCellStyle(headerStyle);
        c2.setCellValue("技术要求");
        HSSFCell c3 = r0.createCell(3);
        c3.setCellStyle(headerStyle);
        c3.setCellValue("招聘人数");
        HSSFCell c4 = r0.createCell(4);
        c4.setCellStyle(headerStyle);
        c4.setCellValue("部门名称");
        HSSFCell c5 = r0.createCell(5);
        c5.setCellStyle(headerStyle);
        c5.setCellValue("申请人");
        HSSFCell c6 = r0.createCell(6);
        c6.setCellStyle(headerStyle);
        c6.setCellValue("创建时间");
        HSSFCell c7 = r0.createCell(7);
        c7.setCellStyle(headerStyle);
        c7.setCellValue("更新时间");

        //遍历放入数据
        for (int i = 0; i < list.size(); i++) {
            Enlist enlist = list.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(enlist.getId());
            row.createCell(1).setCellValue(enlist.getEncruitName());
            row.createCell(2).setCellValue(enlist.getEncruitRequire());
            row.createCell(3).setCellValue(enlist.getEncruitCount());
            row.createCell(4).setCellValue(enlist.getDepartment().getName());
            row.createCell(5).setCellValue(enlist.getApplyName());
            HSSFCell cell6 = row.createCell(6);
            cell6.setCellStyle(dateCellStyle);
            cell6.setCellValue(enlist.getCreateDate());
        }

        //下载
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers = new HttpHeaders();
        try {
            headers.setContentDispositionFormData("attachment", new String("招聘计划表.xls".getBytes("UTF-8"), "ISO-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    //enroll Enroll2Excel
    public static ResponseEntity<byte[]> Enroll2Excel(List<Enroll> list) {
        //1.创建excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建文档摘要
        workbook.createInformationProperties();
        //3.获取并配置文档信息
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        //文档类别
        docInfo.setCategory("员工信息");
        //文档管理员
        docInfo.setManager("tlgen");
        //设置公司信息
        docInfo.setCompany("com.tlgen.com");
        //4.获取文档摘要信息
        SummaryInformation summInfo = workbook.getSummaryInformation();
        //文档标题
        summInfo.setTitle("员工信息表");
        //作者
        summInfo.setAuthor("tlgen");
        //备注
        summInfo.setComments("本文档由 tlgen 提供");

        //5.创建样式
        //标题行样式
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setFillBackgroundColor(IndexedColors.YELLOW.index); //背景色
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //填充模式
        HSSFCellStyle dateCellStyle = workbook.createCellStyle(); //日期格式
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
        HSSFSheet sheet = workbook.createSheet("学生报名信息表");
        //设置列的宽度
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 12 * 256);
        sheet.setColumnWidth(4, 12 * 256);
        sheet.setColumnWidth(5, 20 * 256);
        sheet.setColumnWidth(6, 10 * 256);
        sheet.setColumnWidth(7, 10 * 256);
        sheet.setColumnWidth(8, 16 * 256);

        //6. 创建标题行
        HSSFRow r0 = sheet.createRow(0);
        HSSFCell c0 = r0.createCell(0);
        c0.setCellStyle(headerStyle);
        c0.setCellValue("编号");
        HSSFCell c1 = r0.createCell(1);
        c1.setCellStyle(headerStyle);
        c1.setCellValue("岗位名称");
        HSSFCell c2 = r0.createCell(2);
        c2.setCellStyle(headerStyle);
        c2.setCellValue("技能描述");
        HSSFCell c3 = r0.createCell(3);
        c3.setCellStyle(headerStyle);
        c3.setCellValue("申请人");
        HSSFCell c4 = r0.createCell(4);
        c4.setCellStyle(headerStyle);
        c4.setCellValue("创建时间");
        HSSFCell c5 = r0.createCell(5);
        c5.setCellStyle(headerStyle);
        c5.setCellValue("更新时间");

        //遍历放入数据
        for (int i = 0; i < list.size(); i++) {
            Enroll enroll = list.get(i);
            HSSFRow row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(enroll.getId());
            row.createCell(1).setCellValue(enroll.getPositionName());
            row.createCell(2).setCellValue(enroll.getTechnicalDesc());
            row.createCell(3).setCellValue(enroll.getApplyName());
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellStyle(dateCellStyle);
            cell4.setCellValue(enroll.getCreateDate());
            HSSFCell cell5 = row.createCell(5);
            cell5.setCellStyle(dateCellStyle);
            cell5.setCellValue(enroll.getUpdateDate());
        }

        //下载
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
        HttpHeaders headers = new HttpHeaders();
        try {
            headers.setContentDispositionFormData("attachment", new String("学生报名表.xls".getBytes("UTF-8"), "ISO-8859-1"));
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            workbook.write(baos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
    }

    //enlist excel2enlist
    public static List<Enlist> excel2enlist(MultipartFile file, List<Department> allDepartments) {
        List<Enlist> list = new ArrayList<>();
        Enlist enlist = null;
        try {
            //1. 创建一个 workbook 对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            //2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                //3. 获取表单
                HSSFSheet sheet = workbook.getSheetAt(i);
                //4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //5. 跳过标题行
                    if (j == 0) {
                        continue;//跳过标题行
                    }
                    //6. 获取行
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//防止数据中间有空行
                    }
                    //7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    enlist = new Enlist();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            case STRING:
                                String cellValue = cell.getStringCellValue();
                                switch (k) {
                                    case 1:
                                        enlist.setEncruitName(cellValue);
                                        break;
                                    case 2:
                                        enlist.setEncruitRequire(cellValue);
                                        break;
                                    case 3:
                                        int departmentIndex = allDepartments.indexOf(new Department(cellValue));
                                        enlist.setDepartmentId(allDepartments.get(departmentIndex).getId());
                                        break;
                                    case 5:
                                        enlist.setApplyName(cellValue);
                                        break;
                                    default: {
                                        switch (k) {
                                            case 6:
                                                enlist.setCreateDate(cell.getDateCellValue());
                                                break;
                                            case 7:
                                                enlist.setUpdateDate(cell.getDateCellValue());
                                                break;
                                        }
                                    }
                                    break;
                                }
                        }
                        list.add(enlist);
                    }
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    //enroll excel2Enroll
    public static List<Enroll> excel2Enroll(MultipartFile file) {
        List<Enroll> list = new ArrayList<>();
        Enroll enroll = null;
        try {
            //1. 创建一个 workbook 对象
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            //2. 获取 workbook 中表单的数量
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                //3. 获取表单
                HSSFSheet sheet = workbook.getSheetAt(i);
                //4. 获取表单中的行数
                int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < physicalNumberOfRows; j++) {
                    //5. 跳过标题行
                    if (j == 0) {
                        continue;//跳过标题行
                    }
                    //6. 获取行
                    HSSFRow row = sheet.getRow(j);
                    if (row == null) {
                        continue;//防止数据中间有空行
                    }
                    //7. 获取列数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    enroll = new Enroll();
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        HSSFCell cell = row.getCell(k);
                        switch (cell.getCellType()) {
                            case STRING:
                                String cellValue = cell.getStringCellValue();
                                switch (k) {
                                    case 1:
                                        enroll.setPositionName(cellValue);
                                        break;
                                    case 2:
                                        enroll.setTechnicalDesc(cellValue);
                                        break;
                                    case 3:
                                        enroll.setApplyName(cellValue);
                                        break;
                                    default: {
                                        switch (k) {
                                            case 4:
                                                enroll.setCreateDate(cell.getDateCellValue());
                                                break;
                                            case 5:
                                                enroll.setUpdateDate(cell.getDateCellValue());
                                                break;
                                        }
                                    }
                                    break;
                                }
                        }
                        list.add(enroll);
                    }
                }

            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }
}
